package com.thti.mecool.client.db;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class DataBase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "mecool.db";
	public static final int DATABASE_VERSION = 1;

	public DataBase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}

	/**
	 * 创建数据库
	 * 
	 * @param context
	 *            Content
	 * @param name
	 *            数据库名
	 * @param cursorFactory
	 *            游标工厂
	 * @param version
	 *            数据库版本号
	 */
	public DataBase(Context context, String name, CursorFactory cursorFactory,
			int version) {
		super(context, name, cursorFactory, version);
	}

	// 创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 升级下载
		db.execSQL("CREATE TABLE IF NOT EXISTS filedownlog (id integer primary key autoincrement, downpath varchar(100), threadid INTEGER, downlength INTEGER)");
		// 同步管理
		db.execSQL("CREATE TABLE IF NOT EXISTS synchro (id integer primary key autoincrement,tablename varchar(100),cid INTEGER,surfaceID INTEGER, url varchar(400),commend varchar(400))");
		db.execSQL("CREATE TABLE IF NOT EXISTS photo (id integer primary key autoincrement,path varchar(400))");
		// 沟通管理
		db.execSQL("CREATE TABLE communication(id integer primary key autoincrement,userId varchar(20),communicationId varchar(20),projId  varchar(20),projStoreId  varchar(20),title  varchar(100),type  varchar(100),contactName  varchar(100),contactPosition  varchar(100),contactPhoneOrOther  varchar(100),content  varchar(200))");
		// 工作备忘
		db.execSQL("CREATE TABLE workmemo(id integer primary key autoincrement,userId varchar(20),memoId  varchar(20),channelCode  varchar(20),content  varchar(300),channelName  varchar(50),recordDate DATETIME)");
		// 计划
		db.execSQL("CREATE TABLE plan(id integer primary key autoincrement,userId varchar(20),planId  varchar(20),planName  varchar(100),isFlag  integer,isType integer,planTime varchar(20),status varchar(10),denyNotes varchar(200),changeNotes varchar(200),type varchar(50),createTime DATETIME)");
		// 计划项目
		db.execSQL("CREATE TABLE project(id integer primary key autoincrement,userId varchar(20),projectId  varchar(20),projectName  varchar(100),planId  varchar(20),isType integer,createTime DATETIME)");
		// 计划任务
		db.execSQL("CREATE TABLE task(id integer primary key autoincrement,userId varchar(20),taskId  varchar(20),taskName  varchar(100),projectId  varchar(20),planId varchar(20),startTime varchar(20),endTime varchar(20),storeId varchar(25),storeName varchar(50),isType varchar(20),isFinish INTEGER)");
		// 获取服务器的项目、终端、任务列表
		db.execSQL("CREATE TABLE projectServer(id integer primary key autoincrement, projectId varchar(20),name  varchar(50),taskId  varchar(20),storeId varchar(20),isType integer,requiredCount integer,finishedCount integer,criticalJob integer)");

		db.execSQL("CREATE TABLE taskManage(id integer primary key autoincrement, planId varchar(20),projectId varchar(20),taskId  varchar(20),stepId varchar(20),data varchar(500),isType integer,images  BLOB(65535),isUpload INTEGER,latitude varchar(50),longitude varchar(50),createTime DATETIME)");

		db.execSQL("CREATE TABLE workmemoChannel(id integer primary key autoincrement, code varchar(100),name varchar(100))");

		db.execSQL("CREATE TABLE taskStep(id integer primary key autoincrement, planId varchar(20),projectId varchar(20),taskId varchar(20),stepId varchar(20),parentId varchar(20),isType integer,actionId integer,name varchar(100),data varchar(500),images  BLOB(65535),isUpload INTEGER,latitude varchar(50),longitude varchar(50),isFinsh INTEGER,isOver INTEGER,createTime DATETIME)");
		// 卖进管理项目表
		db.execSQL("CREATE TABLE saleproject(id integer primary key autoincrement, userId varchar(20),projectId varchar(20),name  varchar(50),storeId varchar(20),updateTime varchar(20),isType varchar(20))");
		// 卖进管理终端表
		db.execSQL("CREATE TABLE salestore(id integer primary key autoincrement,storeId varchar(20),projectId varchar(20),name  varchar(50),provinceAdName varchar(100),provinceAdCode varchar(100),cityAdName varchar(100),cityAdCode varchar(100),status INTEGER,tgStockBaseDesc INTEGER,executionStatus INTEGER,problemType INTEGER,problemDescription varchar(300),description varchar(300),lastModified varchar(300))");
		// 卖进管理销售人员信息表
		db.execSQL("CREATE TABLE salers(id integer primary key autoincrement, storeId varchar(20),name varchar(50),phone varchar(50),idCardNo varchar(50),onDutyTime varchar(50),offTime varchar(50))");
		// 消息管理
		db.execSQL("create table messages(messageID INTEGER PRIMARY KEY AUTOINCREMENT,id varchar(20),head VARCHAR(50),body VARCHAR(500),priority VARCHAR(10),type varchar(10),createdOn varchar(20))");
	}

	@Override
	/**
	 * 新新版本
	 * @param SQLiteDatabase  数据库名 
	 * @param oldVersion      老数据库版本号
	 * @param newVersion      新数据库版本号
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/**
	 * 打开数据库
	 */
	public boolean onCreate() {
		this.getWritableDatabase();
		// openDatabase();
		return true;
	}

	/**
	 * 插入新数据
	 * 
	 * @param tableName
	 *            表名
	 * @param values
	 *            返回数据包含的列信息
	 */
	public long insert(String tableName, ContentValues values) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.insert(tableName, null, values);
		return 0;
	}

	/**
	 * 
	 * @param tableName
	 *            表名
	 * @param projection
	 *            返回数据包含的列信息，String数组里放的都是列名
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容，例如：tage>?
	 * @param selectionArgs
	 *            如果你在参数3里写了?（知道我为什么写tage>?了吧），那个这里就是代替?的值 接上例：new String[]{"30
	 * @param groupBy
	 *            分组
	 * @param having
	 *            having
	 * @param sortOrder
	 *            orderBy排序
	 * @return
	 */
	public Cursor query(String tableName, String[] projection,
			String selection, String[] selectionArgs, String groupBy,
			String having, String sortOrder) {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		SQLiteQueryBuilder sb = new SQLiteQueryBuilder();
		sb.setTables(tableName);
		Cursor c = sb.query(db, projection, selection, selectionArgs, groupBy,
				having, sortOrder);

		return c;
	}

	/**
	 * 数据更新
	 * 
	 * @param tableName
	 *            表名
	 * @param values
	 *            返回数据包含的列信息
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容
	 */
	public int update(String tableName, ContentValues values, String selection,
			String[] selectionArgs) {
		// TODO Auto-generated method stub
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			db.update(tableName, values, selection, selectionArgs);
			db.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 删除数据
	 * 
	 * @param tableName
	 *            表名
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容
	 */
	public int delete(String tableName, String selection, String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		int d = db.delete(tableName, selection, selectionArgs);
		db.close();
		return d;
	}

	/**
	 * 自定义查询数据
	 * 
	 * @param uri
	 * @param sql
	 * @return
	 */
	public Cursor queryObject(String sql, String[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.rawQuery(sql, selectionArgs);
	}

	/**
	 * 自定义增删改数据
	 */
	public boolean execObject(String sql, Object[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		// db.beginTransaction();
		try {
			db.execSQL(sql, selectionArgs);
			db.close();
			// db.setTransactionSuccessful();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
		// db.endTransaction();
	}

	/**
	 * 事务
	 */
	public void execTransaction(HashMap<String, String[]> args) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
				Iterator<String> it = args.keySet().iterator();
				for (; it.hasNext();) {
					String sql=it.next();
					db.execSQL(sql, args.get(sql));
				}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		db.close();
	}

	/**
	 * 查询总数
	 * 
	 * @param sql
	 * @return
	 */
	public long getCount(String sql, String[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(sql, selectionArgs);
		return c.getLong(0);

	}

	/**
	 * 删除数据表
	 * 
	 * @param tableName
	 */
	@SuppressWarnings("unused")
	private void dropTable(String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "DROP TABLE IF EXISTS " + tableName;
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询所有数据
	 * 
	 * @param cls
	 *            加载类
	 * @param sql
	 *            查询sql
	 * @param selectionArgs
	 *            参数
	 * @return List对象
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List findAll(Class cls, String sql, String[] selectionArgs) {
		List list = new ArrayList();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, selectionArgs);
		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Object o = cls.newInstance();
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						String fieldName = field.getName();
						if (fieldName.equals("serialVersionUID")
								|| cursor.getColumnIndex(fieldName) == -1) {
							continue;
						}
						Method method = cls.getDeclaredMethod("set"
								+ fieldName.substring(0, 1).toUpperCase()
								+ fieldName.substring(1),
								new Class[] { field.getType() });

						if (field.getType().toString().equals("int")
								|| field.getType().toString()
										.endsWith("Integer")) {
							method.invoke(o, cursor.getInt(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().toString()
								.endsWith("String")) {
							method.invoke(o, cursor.getString(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().isArray()) {
							method.invoke(o, cursor.getBlob(cursor
									.getColumnIndex(fieldName)));
						}
					}
					list.add(o);
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				cursor.close();
				db.close();
			}
		}

		return list;
	}

	/**
	 * 
	 * @param cls
	 *            加载类
	 * @param sql
	 *            查询sql
	 * @param selectionArgs
	 *            参数
	 * @return 返回Object对象
	 */
	@SuppressWarnings({ "rawtypes" })
	public Object findByObject(Class cls, String sql, String[] selectionArgs) {
		Object o = null;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			o = cls.newInstance();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Cursor cursor = db.rawQuery(sql, selectionArgs);
		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						String fieldName = field.getName();
						if (fieldName.equals("serialVersionUID")
								|| cursor.getColumnIndex(fieldName) == -1) {
							continue;
						}
						Method method = cls.getDeclaredMethod("set"
								+ fieldName.substring(0, 1).toUpperCase()
								+ fieldName.substring(1),
								new Class[] { field.getType() });
						if (field.getType().toString().equals("int")
								|| field.getType().toString()
										.endsWith("Integer")) {
							method.invoke(o, cursor.getInt(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().toString()
								.endsWith("String")) {
							method.invoke(o, cursor.getString(cursor
									.getColumnIndex(fieldName)));
						}
					}
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				cursor.close();
				db.close();
			}
		}
		return o;

	}

}
